import pymysql
host = 'localhost'
port = 3307
user = 'root'
password = '123456'
database = 'test'
charset = 'utf8'

# 创建数据库的连接对象，连接数据库
db = pymysql.Connect(host=host, port=port, user=user, password=password, database=database, charset=charset)
print('数据库已连接')

# 创建游标对象
cursor = db.cursor()

#创建数据库，准备数据库
# sql="""
# create table t2(id int primary key auto_increment,name varchar(20),password varchar(20))
# """
# try:
#     cursor.execute(sql)#--执行语句
#     db.commit()  # 事务提交(保证sql语句要么全部成功，要么全部失败)
#     print('表格已创建')
# except Exception as e:
#     db.rollback()  # 事务回滚
#     print(e)
# #增加数据
# username='lisi'
# pwd='123'
# sql=f"""
# insert into t2 values(null,'{username}','{pwd}')
# """
# try:
#     cursor.execute(sql)#提交语句
#     db.commit()#事务提交
#     print('数据已经插入')
# except Exception as e:
#     db.rollback()#事务回滚
#     print(e)

# #改
# # 构建sql
# username = 'lisi'
# pwd = '1234'
# sql = f"""
#     update t2 set password = '{pwd}' where name = '{username}'
# """
# # 执行 sql
# try:
#     cursor.execute(sql)
#     db.commit()
#     print('数据已修改')
# except Exception as e:
#     db.rollback()
#     print(e)

# # 删
# # 构建 sql
# username = 'lisi'
# sql = f"""
#     delete from t2 where name='{username}'
# """
# # 执行 sql
# try:
#     cursor.execute(sql)
#     db.commit()
#     print('数据已删除')
# except Exception as e:
#     db.rollback()
#     print(e)

# 查
# 构建 sql
sql = f"""
    select * from t2
"""
# 执行 sql
try:
    rows = cursor.execute(sql)
    data = cursor.fetchall()
    print('数据已查询')
except Exception as e:
    print(e)

# 查询了几行
print(rows)
# 打印一下，记录
print(data)
for row in data:
    print(f'{row[1]}------{row[2]}')
#关闭游标
cursor.close()
#关闭数据库
db.close()